package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import model.DataBase;
import model.Table;
import model.User;
import model.UserEvent;
import model.UserEventDetail;
import utils.JdbcUtil;
import utils.ResultOfInsert;


public class UserEventDao {
	public ResultOfInsert save(UserEvent ue){
		Connection con =null;  
        PreparedStatement pstmt = null;  
        ResultSet rs = null;
        ResultOfInsert roi = new ResultOfInsert();
        ResultSet rsInsert = null;
        roi.setCount(0);
		
		try {
			con = JdbcUtil.getCon();
			String sql="insert into t_userEvent(uid,etId,tId,[desc],pollingTime,customTime,timeSpan,type) values(?,?,?,?,?,?,?,?)";//desc是sql关键字所以，必须加[]
			
			pstmt = con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
			pstmt.setInt(1, ue.getuId());
			pstmt.setInt(2, ue.getEtId());
			pstmt.setInt(3, ue.gettId());
			pstmt.setString(4, ue.getDesc());
			pstmt.setInt(5, ue.getPollingTime());
			pstmt.setString(6, ue.getCustomTime());
			pstmt.setInt(7, ue.getTimeSpan());
			pstmt.setString(8, ue.getType());
			
			roi.setCount(pstmt.executeUpdate());
			rsInsert = pstmt.getGeneratedKeys();
			if(rsInsert!=null&&rsInsert.next()){
				roi.setId(rsInsert.getInt(1));
			}
			
			
			
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			
			JdbcUtil.closeAll(rs, pstmt,con,rsInsert);
		}
		return roi;
	}

	public UserEvent getNew(){
		Connection con =null;  
        PreparedStatement pstmt = null;  
        ResultSet rs = null;
        
		String sql="select top 1 * from t_userEvent order by customTime desc";
		UserEvent ue=null;
		try {
			con = JdbcUtil.getCon();
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			ue = new UserEvent();
			while(rs.next()){
				ue.setId(rs.getInt("id"));
			}
		}catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JdbcUtil.closeAll(rs, pstmt,con);
		}
		return ue;
	}
	
	public int getUserEvent(String uid)  {
		Connection con =null;  
        PreparedStatement pstmt = null;  
        ResultSet rs = null;
        
        int result = 0;
		String sql = "select * from t_userEvent where uid like '" + uid + "'";
		try {
			con = JdbcUtil.getCon();
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if(rs.next()){//有该用户注册事件
				result =  1;
			}
		}catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JdbcUtil.closeAll(rs, pstmt,con);
		}
		return result;//没有该用户注册事件
	}
	
	public UserEventDetail getUserEventDetailById(int id){
		Connection con =null;  
        PreparedStatement pstmt = null;  
        ResultSet rs = null;
        UserEventDetail ued = null;
		String sql = "select ue.id as ueid,t.id as tid,uid,etid,[desc],pollingTime,customTime,timeSpan,type,db.dbName, db.id as dbid,driverName,url,db.userName as dbUsername,db.password as dbPassword,type,  tableName, dbId, u.email,u.phoneNumber"
				+ " from t_userEvent ue ,t_tables t,t_dbs db,t_user u "
				+ " where ue.tId = t.id and t.dbid=db.id and ue.uid = u.id and ue.id = ?";
		System.out.println(sql);
		try {
			con = JdbcUtil.getCon();
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, id);
			System.out.println(sql);
			rs = pstmt.executeQuery();
			if(rs.next()){
				ued = new UserEventDetail();
				UserEvent ue = new UserEvent();
				Table t = new Table();
				DataBase  dataBase = new DataBase();
				User user = new User();
				ued.setUserEvent(ue);
				ued.setTable(t);
				ued.setDataBase(dataBase);
				ued.setUser(user);
				ue.setId(rs.getInt("ueid"));
				ue.setuId(rs.getInt("uId"));
				ue.setEtId(rs.getInt("etId"));
				ue.settId(rs.getInt("tId"));
				ue.setDesc(rs.getString("desc"));
				ue.setPollingTime(rs.getInt("pollingTime"));
				ue.setCustomTime(rs.getString("customTime"));
				ue.setTimeSpan(rs.getInt("timeSpan"));
				ue.setType(rs.getString("type"));
				t.setDbId(rs.getInt("dbId"));
				t.setId(rs.getInt("tId"));
				t.setTableName(rs.getString("tableName"));
				dataBase.setDbName(rs.getString("dbName"));
				dataBase.setId(rs.getInt("dbId"));
				dataBase.setDriver(rs.getString("driverName"));
				dataBase.setUserName(rs.getString("dbUserName"));
				dataBase.setPassword(rs.getString("dbpassword"));
				dataBase.setUrl(rs.getString("url"));
				user.setEmail(rs.getString("email"));
				user.setPhoneNumber(rs.getString("phoneNumber"));
			}
		}catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JdbcUtil.closeAll(rs, pstmt,con);
		}
		return ued;
	}
	

	public int deletefc(String ueid) {
		Connection con =null;  
        PreparedStatement pstmt = null;  
        ResultSet rs = null;
        String sql = "delete from t_eventFieldsCondition where ueid = " + ueid;
		int result = -1;
		try {
			con = JdbcUtil.getCon();
			pstmt = con.prepareStatement(sql);
			result =  pstmt.executeUpdate();
		} catch (SQLException e) {
			return 0;
		}finally{
			JdbcUtil.closeAll(rs, pstmt,con);
		}
		System.out.println(sql);
		return result;
	}
	public int delUe(String ueid) {
		Connection con =null;  
        PreparedStatement pstmt = null;  
        ResultSet rs = null;
        String sql = "delete from t_userEvent where id =" + ueid;
		int result = -1;
		try {
			con = JdbcUtil.getCon();
			pstmt = con.prepareStatement(sql);
			result =  pstmt.executeUpdate();
		} catch (SQLException e) {
			return 0;
		}finally{
			JdbcUtil.closeAll(rs, pstmt,con);
		}
		return result;
	}
	
	
	
}
